package chapter3.test05;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import vo.UserVO;

public class UserDao {
	
	private DataSource dataSource ;
	

	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}
	
	public void add(final UserVO user) throws SQLException{
		
		 class AddStatement implements StatementStrategy {
			@Override
			public PreparedStatement makePreparedStatement(Connection c)
					throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement psmt = c.prepareStatement("insert into users(id,name,password) values (?,?,?)");
				psmt.setString(1, user.getId());
				psmt.setString(2,user.getName());
				psmt.setString(3,user.getPassword());
				return psmt;
				
			}
		}
		
		StatementStrategy stmt = new AddStatement();
		jdbcContextWithStatementStrategy(stmt);
		
	}
		
	public UserVO get(String id) throws SQLException{
		
		Connection c = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		UserVO user = null;
		
		try{
			 c =  dataSource.getConnection();	
			
			 psmt = c.prepareStatement("select * from users where id = ?");
			
			psmt.setString(1, id);
			
			 rs =  psmt.executeQuery();
			
			while(rs.next()){
				user = new UserVO();
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
			}
		}catch(SQLException e){
			throw e;
		}finally{
			if(rs != null){
				try{
						rs.close();
				}catch(SQLException e){
					
				}
			}
			if(psmt != null){
				try{
					psmt.close();
				}catch(SQLException e){
					
				}
			}
			if(c != null){
				try{
						c.close();
				}catch(SQLException e){
					
				}
			}	
		}
		
		
		if(user == null) throw new EmptyResultDataAccessException(1);
		
		return user;
		
	}
	
	public void deleteAll() throws SQLException{
		
		StatementStrategy st = new DeleteAllStatement();
		jdbcContextWithStatementStrategy(st);
		
	}
	
	public int getCount() throws SQLException {
		Connection c = dataSource.getConnection();
		
		PreparedStatement ps = c.prepareStatement("select  count(*) from users");
		
		ResultSet rs = ps.executeQuery();
		rs.next();
		int count  = rs.getInt(1);
		return count;
	}
	

	
	public void  jdbcContextWithStatementStrategy(StatementStrategy stmt)throws SQLException{
		Connection c  = null;
		PreparedStatement ps =null;
		try{
				
			c = dataSource.getConnection();
			ps = stmt.makePreparedStatement(c);
			ps.executeUpdate();
			
		}catch(SQLException e){
			throw e;
		}finally{
			
			if(ps != null){
				try{
					ps.close();
				}catch(SQLException e){
					
				}
			}
			if(c != null){
				try{
						c.close();
				}catch(SQLException e){
					
				}
			}	
		}
	}	
}
